#### Table A9 Script ####

rm(list=ls());gc();gc();gc();gc();gc();gc();gc();gc()


library(gt)
library(Hmisc)
library(tidyverse)
library(dplyr)
library(hrbrthemes)
library(lfe)
library(stargazer)

here::i_am("Scripts/TabA9Script.R")

library(here)



#Load descriptive data

cc4<-readRDS(here("Data", "descriptive_donation_data.rds"))


#reduce secondary data set to finstate3dc (index), date, amount, and recipient (matching vars) and sectors/businesses
cc5<-cc4%>%
  select(finstate3dc, cDATE, cAMOUNT, Candidate2Office2Year,Specific_Business_EDIT3, Broad_Sector_EDIT3, fundraiser)%>%
  unique()



#Limit to just those non-event donors who gave more than once

#Get a designation for event and non-event donors
df<-cc5 %>%
  group_by(finstate3dc)%>%
  dplyr::mutate(edon=ifelse(sum(fundraiser)>0,1,0))

#Find out how many donations each person has

df<-df %>%
  group_by(finstate3dc)%>%
  dplyr::mutate(numdons=n())

tabnumdonsedon<-table(df$edon, df$numdons)

#Also get a total for how much total they've donated

df<-df %>%
  group_by(finstate3dc)%>%
  dplyr::mutate(totdonamt=sum(cAMOUNT))



#What about broad sectoredit3?
dfuniq<-df%>%
  select(finstate3dc, Broad_Sector_EDIT3, edon)%>%
  filter(Broad_Sector_EDIT3!="Uncoded" & Broad_Sector_EDIT3!="")%>%
  unique()


#Now let's do numbers of donors in each sector
dfuniq<-df%>%
  select(finstate3dc, Broad_Sector_EDIT3, edon)%>%
  filter(Broad_Sector_EDIT3!="Uncoded" & Broad_Sector_EDIT3!="")%>%
  unique()



nonev<-sort(table(dfuniq$Broad_Sector_EDIT3[dfuniq$edon==0]), decreasing=T)

dfnon<-as.data.frame(cbind(names(nonev), unname(nonev)))

names(dfnon)<-c("Sector", "NumNEDonors")

dfnon$NumNEDonors<-as.numeric(dfnon$NumNEDonors)

evd<-sort(table(dfuniq$Broad_Sector_EDIT3[dfuniq$edon==1]), decreasing=T)

dfev<-as.data.frame(cbind(names(evd),unname(evd)))

names(dfev)<-c("Sector", "NumEDonors")

dfev$NumEDonors<-as.numeric(dfev$NumEDonors)

#Merge to get crossover

dfall<-merge(dfnon, dfev, by="Sector")


#get a row for the totals in each of these categories
rowfrobind<-as.data.frame(t(c("All Sectors (Total)", sum(dfall$NumNEDonors), sum(dfall$NumEDonors))))
names(rowfrobind)<-names(dfall)


#Bind this to table
dfall2<-rbind(dfall, rowfrobind)

#Make columns numeric
dfall2$NumNEDonors<-as.numeric(dfall2$NumNEDonors)
dfall2$NumEDonors<-as.numeric(dfall2$NumEDonors)

#Calculate ratios

#get baseline:
baseline<-as.numeric(rowfrobind$NumEDonors)/as.numeric(rowfrobind$NumNEDonors)

dfall2$Ratio<-dfall2$NumEDonors/dfall2$NumNEDonors


#Get Multiplier

dfall2$multiple<-dfall2$Ratio/baseline

#Sort by Ratio

dfall3<-dfall2 %>%
  select(Sector, Ratio, everything())%>%
  arrange(-Ratio)


library(xtable)


#TAble A9 production
print(xtable(dfall3, type="latex"), include.rownames=FALSE)

#THIS IS THE BOLD PARTS OF TABLE A9!

#Get specific business with more than 1000 donations
df2<-df%>%
  filter(Broad_Sector_EDIT3!="Uncoded" & Broad_Sector_EDIT3!="" & Specific_Business_EDIT3!="Uncoded" & Specific_Business_EDIT3!="")%>%
  group_by(Broad_Sector_EDIT3,Specific_Business_EDIT3)%>%
  summarise(numdons=n())%>%
  filter(numdons>999)

dfuniq<-df%>%
  select(finstate3dc, Broad_Sector_EDIT3,Specific_Business_EDIT3, edon)%>%
  filter(Broad_Sector_EDIT3!="Uncoded" & Broad_Sector_EDIT3!="" & Specific_Business_EDIT3!="Uncoded" & Specific_Business_EDIT3!="")%>%  
  unique()


dfuniq<-df%>%
  select(finstate3dc, Broad_Sector_EDIT3, Specific_Business_EDIT3,edon)%>%
  filter(Broad_Sector_EDIT3!="Uncoded" & Broad_Sector_EDIT3!="" & Specific_Business_EDIT3!="Uncoded" & Specific_Business_EDIT3!="")%>%
  unique()%>%
  group_by(Broad_Sector_EDIT3,Specific_Business_EDIT3)%>%
  summarise(edonperc=sum(edon), nonedonperc=(n()-sum(edon)), ratioene=((edonperc/nonedonperc)/baseline))%>%
  ungroup%>%
  unique()

#Now focus on only those business in df2 (over 999 donations) to get biggest deviation

dfuniq2<-dfuniq%>%
  filter(Specific_Business_EDIT3 %in% df2$Specific_Business_EDIT3,Specific_Business_EDIT3!="No employer listed or discovered" )%>%
  arrange(Broad_Sector_EDIT3, ratioene)

#From this table, we pull the highlighted businesses in the table

#The specific businesses included in the table can be individually found using the following code:

dfuniq2[which(dfuniq2$Broad_Sector_EDIT3=="Construction"&dfuniq2$Specific_Business_EDIT3=="Public works, industrial & commercial construction"),][c(1,2,5)]

dfuniq2[which(dfuniq2$Broad_Sector_EDIT3=="Lawyers & Lobbyists"&dfuniq2$Specific_Business_EDIT3=="Lobbyists & public relations"),][c(1,2,5)]

dfuniq2[which(dfuniq2$Broad_Sector_EDIT3=="Finance, Insurance & Real Estate"&dfuniq2$Specific_Business_EDIT3=="Accident & health insurance"),][c(1,2,5)]

dfuniq2[which(dfuniq2$Broad_Sector_EDIT3=="Energy & Natural Resources"&dfuniq2$Specific_Business_EDIT3=="Electric power utilities"),][c(1,2,5)]

dfuniq2[which(dfuniq2$Broad_Sector_EDIT3=="General Business"&dfuniq2$Specific_Business_EDIT3=="Heavy industrial manufacturing"),][c(1,2,5)]

dfuniq2[which(dfuniq2$Broad_Sector_EDIT3=="Transportation"&dfuniq2$Specific_Business_EDIT3=="Truck & automotive parts and accessories"),][c(1,2,5)]

dfuniq2[which(dfuniq2$Broad_Sector_EDIT3=="Government Agencies/Education/Other"&dfuniq2$Specific_Business_EDIT3=="Retired"),][c(1,2,5)]

dfuniq2[which(dfuniq2$Broad_Sector_EDIT3=="Labor"&dfuniq2$Specific_Business_EDIT3=="Automotive unions"),][c(1,2,5)]






